package org.crate.query.api;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.lang3.StringUtils;
import org.crate.query.api.utils.BatchConvert;
import org.crate.query.api.utils.BatchInput;

import static org.apache.commons.dbutils.DbUtils.close;

/**
 * Created by huangfei on 2018/9/19.
 */
public class CrateQueryApi {

    public CrateQueryApi(String host) throws SQLException {

    }

    public List<Map<String, Object>> query(String sql) throws SQLException {

        if (StringUtils.isBlank(sql)) {
            throw new NullPointerException("sql required");
        }

        QueryRunner run = new QueryRunner();
        List<Map<String, Object>> result = null;
        Connection conn = null;
        try {
            conn = connection();
            result = run.query(conn, sql, new MapListHandler());
        } finally {
            close(conn);
        }
        return result;
    }

    public List<Map<String, Object>> select(String tablename, Set<String> fields, Map<String, String> whereMap,
            int limit, int offset, boolean useLike, String whereSql) throws SQLException {
        String sql = BatchConvert.selectFields(tablename, fields, whereMap, limit, offset, useLike, whereSql,null);
        return query(sql);
    }

    public List<Map<String, Object>> select(String tablename, Set<String> fields, Map<String, String> whereMap,
                                            int limit, int offset, boolean useLike, String whereSql,String orderBySql) throws SQLException {
        String sql = BatchConvert.selectFields(tablename, fields, whereMap, limit, offset, useLike, whereSql,orderBySql);
        return query(sql);
    }

    public long selectCount(String tablename, Map<String, String> whereMap, boolean useLike, String whereSql)
            throws SQLException {
        String sql = BatchConvert.selectFieldsCount(tablename, whereMap, useLike, whereSql);

        return Long.valueOf(query(sql).get(0).get("c").toString());
    }

    public boolean exists(String tablename, Map<String, String> whereMap)
            throws SQLException {
        String sql = BatchConvert.exists(tablename, whereMap);

        return Long.valueOf(query(sql).get(0).get("c").toString()) > 0;
    }

    public int[] batch(String tablename, Map<String, String> row) throws SQLException {
        List<Map<String, String>> data = new ArrayList<Map<String, String>>(1);
        data.add(row);
        return batch(tablename, data);
    }

    public int[] batch(String tablename, List<Map<String, String>> data) throws SQLException {

        if (data == null || data.isEmpty()) {
            System.err.println("data is empty");
            return null;
        }
        BatchConvert.batchMapSort(data);
        boolean columnSizeIsOk = BatchConvert.batchColumnSizeIsOk(data);
        if (columnSizeIsOk) {
            return batch0(tablename, data);
        } else {
            System.out.println("columnSizeIsOk=" + columnSizeIsOk + ",tablename=" + tablename);
            for (Iterator<Map<String, String>> it = data.iterator(); it.hasNext();) {
                Map<String, String> entry = it.next();

                List<Map<String, String>> row = new ArrayList<Map<String, String>>(1);
                row.add(entry);

                batch0(tablename, row);
            }

            return null;
        }
    }

    private int[] batch0(String tablename, List<Map<String, String>> data) throws SQLException {
        BatchInput batchInput = BatchConvert.convert(tablename, data);

        if (batchInput.getData() == null || batchInput.getData().length <= 0) {
            return null;
        }

        QueryRunner run = new QueryRunner();
        Connection conn = connection();
        int[] inserts = null;
        try {
            inserts = run.batch(conn, batchInput.getSql(), batchInput.getData());
        } finally {
            close(conn);
        }
        return inserts;

    }

    // 批量删除表的部分数据
    public int batchDel(String tablename, Map<String, String> map) throws SQLException {
        if (StringUtils.isBlank(tablename)) {
            throw new NullPointerException("tablename requried");
        }

        String delSql = BatchConvert.convertDel(tablename, map);
        System.out.println("batchDel delSql=" + delSql);

        QueryRunner run = new QueryRunner();
        Connection conn = connection();
        int inserts = 0;
        try {
            inserts = run.update(conn, delSql);
        } finally {
            close(conn);
        }
        return inserts;
    }

    // 清空表的全部数据
//    public int clearTable(String tablename) throws SQLException {
//        if (StringUtils.isBlank(tablename)) {
//            throw new NullPointerException("tablename requried");
//        }
//
//        String delSql = BatchConvert.clearTable(tablename);
//        System.out.println("clearTable delSql=" + delSql);
//
//        QueryRunner run = new QueryRunner();
//        Connection conn = connection();
//        int inserts = 0;
//        try {
//            inserts = run.update(conn, delSql);
//        } finally {
//            close(conn);
//        }
//        return inserts;
//    }

    public int updateUnit(String sonum, String desc) throws SQLException {
        QueryRunner run = new QueryRunner();
        Connection conn = connection();
        int update = 0;
        try {
            update = run.update(conn, "update sap_hu_zprd_hu set punit='" + desc + "' where sonum='" + sonum + "'");
        } finally {
            close(conn);
        }
        return update;
    }

    Connection connection() throws SQLException {
        return DBPoolConnection.getInstance().getConnection();
    }

    public int updateUq(String tablename, Map<String, String> row, Map<String, String> whereMap) throws SQLException {
        String sql = BatchConvert.convertUpdateUq(tablename, row, whereMap);

        QueryRunner run = new QueryRunner();
        Connection conn = connection();
        try {
            return run.update(conn, sql);
        } finally {
            close(conn);
        }
    }

    public int execute(String sql) throws SQLException {
        QueryRunner run = new QueryRunner();
        Connection conn = connection();
        try {
            return run.execute(conn,sql,new Object[]{});
        } finally {
            close(conn);
        }
    }

    public int executeUpdate(String sql) throws SQLException {
        QueryRunner run = new QueryRunner();
        Connection conn = connection();
        try {
            return run.update(conn,sql);
        } finally {
            close(conn);
        }
    }

    public boolean refreshTable(String tableName) throws SQLException {
        if(StringUtils.isBlank(tableName)){
            throw new NullPointerException();
        }

        QueryRunner run = new QueryRunner();
        Connection conn = connection();
        try {
            return run.update(conn,"REFRESH TABLE " + tableName) == 0;
        } finally {
            close(conn);
        }
    }

}
